const express = require('express');
const mysql = require('mysql2');
const bodyParser = require('body-parser');
const app = express();

// 中间件必须在所有路由之前
app.use(express.json());
app.use(express.urlencoded({ extended: false }));

// 引入 multer 中间件
var multer = require('multer');
// 配置 multer，设置文件上传的存储方式和目录
// 这里配置将文件存储在服务器本地的 'uploads' 文件夹中
// 请确保你的后端项目根目录下有一个名为 'uploads' 的文件夹
var storage = multer.diskStorage({
  destination: function (req, file, cb) {
    // 文件上传成功后存储的文件夹
    // __dirname 是当前文件所在的目录，我们在这里假设 'uploads' 文件夹在 server.js 同级
    cb(null, './uploads'); 
  },
  filename: function (req, file, cb) {
    // 文件上传成功后文件名
    // 使用时间戳和原始文件名，确保文件名唯一
    cb(null, Date.now() + '-' + file.originalname);
  }
});
// 创建 multer 实例，配置 storage
var upload = multer({ storage: storage });

var connection = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password: '167321',
  port: '3306',
  database: 'life_habit',
  connectTimeout: 10000, // 连接超时时间
  waitForConnections: true, // 等待连接
  connectionLimit: 10, // 连接限制
  queueLimit: 0 // 队列限制
});

// 添加错误处理
connection.on('error', function(err) {
  console.error('数据库连接错误:', err);
  if(err.code === 'PROTOCOL_CONNECTION_LOST') {
    console.log('数据库连接丢失，尝试重新连接...');
    connection.connect();
  } else if(err.code === 'ER_ACCESS_DENIED_ERROR') {
    console.error('数据库访问被拒绝，请检查用户名和密码');
  } else {
    console.error('数据库错误:', err);
  }
});

// 修改连接方式
connection.connect(function(err) {
  if (err) {
    console.error('数据库连接失败:', err);
    return;
  }
  console.log('数据库连接成功');
});

const port = 3001;
app.listen(port, () => {
  console.log(`服务器运行在端口 ${port}`);
});
app.use('/uploads', express.static('uploads'));

// 计时记录接口
app.post('/api/timer', (req, res) => {
  // 参数校验
  const { habit_id, user_id, timer_SetTime, timer_ActualTime, timer_Year, timer_Month, timer_Day, timer_type } = req.body;
  if (
    habit_id === undefined ||
    user_id === undefined ||
    timer_SetTime === undefined ||
    timer_ActualTime === undefined ||
    timer_Year === undefined ||
    timer_Month === undefined ||
    timer_Day === undefined ||
    timer_type === undefined
  ) {
    return res.status(400).json({ error: '参数不完整' });
  }

  // 这里定义sql
  const sql = `
    INSERT INTO timer
    (habit_id, user_id, timer_SetTime, timer_ActualTime, timer_Year, timer_Month, timer_Day, timer_type)
    VALUES (?, ?, ?, ?, ?, ?, ?, ?)
  `;

  // 这里用sql
  connection.query(
    sql,
    [habit_id, user_id, timer_SetTime, timer_ActualTime, timer_Year, timer_Month, timer_Day, timer_type],
    (err, result) => {
      if (err) {
        console.error('数据库错误:', err);
        return res.status(500).json({ error: '数据库错误', detail: err });
      }
      res.json({ success: true, id: result.insertId });
    }
  );
});

// 获取计时记录接口
app.get('/getTimer', (req, res) => {
  const userId = req.query.user_id;
  // 最终的 SQL 查询，确保同时匹配 habit_id 和 user_id，并解决 collation 不匹配问题
  const sql = `
    SELECT t.*, h.habit_name, h.habit_img 
    FROM timer t
    LEFT JOIN user_habit h ON t.habit_id = h.habit_id AND CAST(t.user_id AS CHAR) COLLATE utf8mb4_unicode_ci = h.user_id COLLATE utf8mb4_unicode_ci
    WHERE CAST(t.user_id AS CHAR) COLLATE utf8mb4_unicode_ci = ? COLLATE utf8mb4_unicode_ci
    ORDER BY t.timer_Year DESC, t.timer_Month DESC, t.timer_Day DESC, t.timer_id DESC
  `;
  connection.query(sql, [userId], (err, results) => {
    if (err) {
      console.error('获取计时记录失败 (解决 collation 问题):', err); // 修改日志信息
      res.status(500).json({ error: '获取计时记录失败' });
      return;
    }
    console.log('获取到的计时记录 (解决 collation 问题):', results); // 修改日志信息
    res.json(results);
  });
});

//登录
app.post('/login', (req, res) => {
  const { userId, password } = req.body;
  console.log(userId);
  connection.query('SELECT * FROM user WHERE user_id=? and password=?', [userId, password], (err, result) => {
    if (err) {
      console.error(err);
      res.status(500).send('数据库查询出错');
      return;
    }
    res.send(result); // 将查询结果返回给小程序端
  });
});

//获取用户习惯数据
app.get('/getUserHabit', (req, res) => {
  const user_id = req.query.userId;
  console.log(user_id);
  connection.query('SELECT habit_id, user_id, habit_name, habit_img, habit_createdTime, habit_finishedTime, habit_customTime, habit_num, isClockedInToday, is_finished, punch_in_scenarios, is_important, habit_frequency_type, habit_frequency_value, habit_frequency_days FROM user_habit WHERE user_id=?', [user_id], (err, result) => {
    if (err) {
      console.error(err);
      res.status(500).send('数据库查询出错');
      return;
    }
    res.send(result); // 将查询结果返回给小程序端
  });
});

//在习惯页删除用户习惯数据 (修改为使用事务)
app.post('/deleteHabit', (req, res) => {
  const { habitId, userId } = req.body;
  console.log('Deleting habit:', habitId, 'for user:', userId);

  connection.beginTransaction(err => {
    if (err) {
      console.error('开启事务失败:', err);
      return res.status(500).json({ success: false, message: '开启事务失败' });
    }

    // 1. 删除与该习惯相关的打卡日志记录 (log_type = 1)
    const deleteLogsSql = 'DELETE FROM log WHERE habit_id = ? AND user_id = ? AND log_type = 1';
    connection.query(deleteLogsSql, [habitId, userId], (err, logResult) => {
      if (err) {
        console.error('删除打卡日志记录失败:', err);
        return connection.rollback(() => {
          res.status(500).json({ success: false, message: '删除打卡日志记录失败' });
        });
      }

      console.log(`删除打卡日志记录影响行数: ${logResult.affectedRows}`);

      // 2. 删除用户习惯记录
      const deleteHabitSql = 'DELETE FROM user_habit WHERE habit_id = ? AND user_id = ?';
      connection.query(deleteHabitSql, [habitId, userId], (err, habitResult) => {
        if (err) {
          console.error('删除习惯记录失败:', err);
          return connection.rollback(() => {
            res.status(500).json({ success: false, message: '删除习惯记录失败' });
          });
        }

        console.log(`删除习惯记录影响行数: ${habitResult.affectedRows}`);

        // 3. 检查是否成功删除习惯（至少影响一行），并提交事务
        if (habitResult.affectedRows > 0) {
          connection.commit(err => {
            if (err) {
              console.error('提交事务失败:', err);
              return connection.rollback(() => {
                res.status(500).json({ success: false, message: '提交事务失败' });
              });
            }
            res.json({ success: true, message: '习惯及相关打卡记录删除成功' });
          });
        } else {
          // 如果没有找到要删除的习惯，也回滚事务 (或者根据需求选择是否回滚)
           connection.rollback(() => {
             res.json({ success: false, message: '未找到要删除的习惯' });
           });
        }
      });
    });
  });
});

//获取习惯库分类数据
app.get('/data', (req, res) => {
  connection.query('SELECT * FROM habit_category', (err, result) => {
    if (err) throw err;
    res.send(result); // 将查询结果返回给小程序端
  });
});
//获取习惯库习惯数据
app.get('/habitData', (req, res) => {
  const habit_category_id = req.query.habit_category_id;
  console.log('查询习惯数据，分类ID:', habit_category_id);

  connection.query('SELECT * FROM habits where habit_category_id=?', [habit_category_id], (err, result) => {
    if (err) {
      console.error('查询错误:', err);
      return res.status(500).send(err);
    }
    console.log('查询结果:', result);
    res.send(result);
  });
});

// 更新用户习惯天数
app.post('/updateHabitNum', (req, res) => {
  const {
    habit_id,
    habit_num,
    user_id,
    isClockedInToday
  } = req.body;
  
  // 添加日志输出
  console.log('Received update request:', {
    habit_id,
    habit_num,
    user_id,
    isClockedInToday
  });

  // 确保类型转换
  const habitId = parseInt(habit_id);
  const habitNum = parseInt(habit_num);
  const userId = String(user_id);
  const clockedIn = isClockedInToday ? 1 : 0;

  // 使用参数化查询并添加字符集处理
  const sql = `
    UPDATE user_habit 
    SET habit_num = ?, 
        isClockedInToday = ? 
    WHERE habit_id = ? 
    AND CAST(user_id AS CHAR) COLLATE utf8mb4_unicode_ci = ? COLLATE utf8mb4_unicode_ci
  `;

  connection.query(
    sql,
    [habitNum, clockedIn, habitId, userId],
    (err, result) => {
      if (err) {
        console.error('数据库更新习惯天数出错:', err);
        return res.status(500).json({
          success: false,
          message: '数据库更新出错',
          error: err.message
        });
      }

      console.log('更新结果:', result);

      if (result.affectedRows > 0) {
        res.json({
          success: true,
          message: '习惯天数和状态更新成功',
          updatedRows: result.affectedRows
        });
      } else {
        console.warn('未找到匹配的习惯记录:', {
          habit_id: habitId,
          user_id: userId
        });
        res.json({
          success: false,
          message: '未找到匹配的习惯记录或数据无变化'
        });
      }
    }
  );
});

// 新增打卡记录
app.post('/addClorkRecord', (req, res) => {
  const { habit_id, user_id, clork_year,clork_month,clork_day,clork_time, clork_status } = req.body;
  // clork_status: 1=完成, 0=失败
  if (!habit_id || !user_id || !clork_time || clork_status === undefined) {
    return res.status(400).json({
      success: false,
      message: '缺少必要参数'
    });
  }
  const sql = 'INSERT INTO clork_record (habit_id, user_id, clork_year,clork_month,clork_day,clork_time, clork_status) VALUES (?, ?, ?, ?, ?, ?, ?)';
  const values = [habit_id, user_id, clork_year,clork_month,clork_day,clork_time, clork_status];
  connection.query(sql, values, (err, result) => {
    if (err) {
      console.error('打卡记录插入失败:', err);
      res.status(500).json({
        success: false,
        message: '打卡记录插入失败'
      });
      return;
    }
    res.json({
      success: true,
      message: '打卡记录插入成功',
      clork_id: result.insertId
    });
  });
});

// 添加日志
app.post('/addLog', (req, res) => {
  console.log('req.body:', req.body);
  const {
    log_content,
    habit_id,
    user_id,
    log_type,
    log_year,
    log_month,
    log_day,
    log_CreatedTime,
    log_img
  } = req.body;

  console.log('Adding log:', {
    user_id,
    log_content,
    habit_id,
    log_type,
    log_year,
    log_month,
    log_day,
    log_CreatedTime,
    log_img
  });

  // 参数验证 - 只验证必要参数，允许内容为空
  if (!habit_id || !user_id || log_type === undefined || !log_year || !log_month || !log_day) {
    console.error('Missing required parameters:', { habit_id, user_id, log_type, log_year, log_month, log_day });
    return res.status(400).json({
      success: false,
      message: '缺少必要参数'
    });
  }

  // 构建 SQL 语句
  const sql = `
    INSERT INTO log (
      log_content, 
      habit_id, 
      user_id, 
      log_type, 
      log_year, 
      log_month, 
      log_day, 
      log_CreatedTime, 
      log_img
    ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
  `;

  const values = [
    log_content || '',  // 允许空内容
    habit_id,
    user_id,
    log_type,
    log_year,
    log_month,
    log_day,
    log_CreatedTime || new Date().toISOString().slice(0, 19).replace('T', ' '),
    log_img || ''  // 允许空图片
  ];

  console.log('Executing SQL:', sql);
  console.log('With values:', values);

  // 执行 SQL
  connection.query(sql, values, (err, result) => {
    if (err) {
      console.error('日志插入失败:', err);
      return res.status(500).json({
        success: false,
        message: '日志插入失败',
        error: err.message
      });
    }

    console.log('日志插入成功:', result);
    res.json({
      success: true,
      message: '日志插入成功',
      logId: result.insertId
    });
  });
});



// 获取用户习惯统计数据
app.get('/getHabitStats', (req, res) => {
  const userId = req.query.userId;
  if (!userId) {
    return res.status(400).json({
      error: '缺少用户ID'
    });
  }

  const sql = `
    SELECT habit_name, habit_num, habit_img 
    FROM user_habit 
    WHERE user_id = ? 
    ORDER BY habit_num DESC
  `;

  connection.query(sql, [userId], (err, results) => {
    if (err) {
      console.error('获取习惯统计数据失败:', err);
      return res.status(500).json({
        error: '获取习惯统计数据失败'
      });
    }
    res.json(results);
  });
});

// 获取最后一个用户的账号
app.get('/user/last', (req, res) => {
  const sql = 'SELECT user_id FROM user ORDER BY id DESC LIMIT 1';
  
  connection.query(sql, (err, results) => {
    if (err) {
      console.error('获取最后一个用户账号失败:', err);
      return res.status(500).json({
        error: '获取最后一个用户账号失败'
      });
    }
    res.json(results);
  });
});

// 用户注册
app.post('/register', (req, res) => {
  const { userId, password } = req.body;
  
  // 检查是否提供了必要的参数
  if (!userId || !password) {
    return res.status(400).json({
      success: false,
      message: '缺少必要参数'
    });
  }

  // 拼接用户名
  const userName = `用户${userId}`;

  // 插入新用户，包含 user_name 字段
  const sql = 'INSERT INTO user (user_id, user_name, password, role) VALUES (?, ?, ?, ?)';
  connection.query(sql, [userId, userName, password, '用户'], (err) => {
    if (err) {
      console.error('注册失败:', err);
      return res.status(500).json({
        success: false,
        message: '注册失败'
      });
    }

    res.json({
      success: true,
      message: '注册成功'
    });
  });
});

// 获取指定日期的日志数据
app.get('/getAllLogs', (req, res) => {
  const { userId, year, month, day } = req.query;
  
  if (!userId || !year || !month || !day) {
    return res.status(400).json({
      error: '缺少必要参数'
    });
  }

  const habitsSql = 'SELECT habit_id, habit_name, habit_img FROM user_habit WHERE user_id = ?';
  
  connection.query(habitsSql, [userId], (err, habits) => {
    if (err) {
      console.error('获取用户习惯信息失败:', err);
      return res.status(500).json({
        error: '获取用户习惯信息失败'
      });
    }

    const logsSql = 'SELECT * FROM log WHERE user_id = ? AND log_year = ? AND log_month = ? AND log_day = ?';
    
    connection.query(logsSql, [userId, year, month, day], (err, logs) => {
      if (err) {
        console.error('获取用户日志失败:', err);
        return res.status(500).json({
          error: '获取用户日志失败'
        });
      }

      const logsWithHabitInfo = logs.map(log => {
        const habit = habits.find(h => h.habit_id == log.habit_id);
        return {
          ...log,
          habit_name: habit ? habit.habit_name : '未知习惯',
          habit_img: habit ? habit.habit_img : ''
        };
      });

      res.json(logsWithHabitInfo);
    });
  });
});

// 获取指定日期的日记数据
app.get('/getDiaryLogsByDate', (req, res) => {
  const { userId, year, month, day } = req.query;
  console.log('获取日记数据，参数:', { userId, year, month, day });

  if (!userId || !year || !month || !day) {
    console.log('参数不完整');
    return res.status(400).json({
      error: '缺少必要参数'
    });
  }

  // 从 note 表中查询指定日期的日记
  const sql = 'SELECT * FROM note WHERE user_id = ? AND note_year = ? AND note_month = ? AND note_day = ? ORDER BY created_time DESC';
  const values = [userId, year, month, day];
  
  console.log('执行SQL:', sql);
  console.log('SQL参数:', values);

  connection.query(
    sql,
    values,
    (err, result) => {
      if (err) {
        console.error('获取日记失败:', err);
        return res.status(500).json({
          success: false,
          message: '获取日记失败',
          error: err.message
        });
      }
      console.log('查询结果:', result);
      res.json(result);
    }
  );
});

// 新增日记笔记
app.post('/addNote', (req, res) => {
  const { userId, content, note_year, note_month, note_day } = req.body;

  if (!userId || !content || !note_year || !note_month || !note_day) {
    return res.status(400).json({
      success: false,
      message: '缺少必要参数'
    });
  }

  const sql = 'INSERT INTO note (user_id, content, note_year, note_month, note_day) VALUES (?, ?, ?, ?, ?)';
  const values = [userId, content, note_year, note_month, note_day];

  connection.query(
    sql,
    values,
    (err, result) => {
      if (err) {
        console.error('日记插入失败:', err);
        res.status(500).json({
          success: false,
          message: '日记插入失败'
        });
        return;
      }
      console.log('日记插入成功:', result);
      res.json({
        success: true,
        message: '日记插入成功',
        noteId: result.insertId // 返回新插入记录的ID
      });
    }
  );
});

// 更新用户习惯状态 
app.post('/updateHabitStatus', (req, res) => {
  const { userId, habitId, is_finished } = req.body;

  if (!userId || !habitId || is_finished === undefined) { // is_finished 可以是 0 或 1，所以检查 undefined
    return res.status(400).json({
      success: false,
      message: '缺少必要参数'
    });
  }

  // 构建SQL更新语句
  const sql = 'UPDATE user_habit SET is_finished = ? WHERE user_id = ? AND habit_id = ?';
  const values = [is_finished, userId, habitId];

  connection.query(
    sql,
    values,
    (err, result) => {
      if (err) {
        console.error('更新习惯状态失败:', err);
        res.status(500).json({
          success: false,
          message: '更新习惯状态失败'
        });
        return;
      }

      console.log('更新习惯状态成功:', result);

      // 检查是否有一行被影响，表示更新成功
      if (result.affectedRows > 0) {
        res.json({
          success: true,
          message: '习惯状态更新成功'
        });
      } else {
        // 如果affectedRows为0，可能user_id或habit_id不存在匹配项
        console.warn('未找到匹配的习惯记录进行状态更新:', { userId, habitId });
        res.json({
          success: false,
          message: '未找到匹配的习惯记录'
        });
      }
    }
  );
});

// 编写获取用户详情接口 
app.get('/getUserDetails', (req, res) => {
    const userId = req.query.userId;

    if (!userId) {
        return res.status(400).json({ success: false, message: '缺少用户ID' });
    }

    const sql = 'SELECT user_id, user_name, user_img, phone_number, sex, role FROM user WHERE user_id = ?';
    connection.query(sql, [userId], (err, results) => {
        if (err) {
            console.error('获取用户详情失败:', err);
            return res.status(500).json({ success: false, message: '获取用户详情失败' });
        }

        if (results.length > 0) {
            // 假设只返回匹配的第一个用户（user_id应该是唯一的）
            res.json({ success: true, userDetails: results[0] });
        } else {
            res.status(404).json({ success: false, message: '未找到用户' });
        }
    });
});

// 编写更新用户详情接口
app.post('/updateUserDetails', (req, res) => {
    // 在这里也接收 user_img 字段
    const { user_id, user_name, phone_number, sex, user_img } = req.body;

    // 参数校验，确保user_id存在
    if (!user_id) {
        return res.status(400).json({ success: false, message: '缺少用户ID' });
    }

    // 构建动态更新SQL语句
    let sql = 'UPDATE user SET';
    const values = [];
    const updates = [];

    if (user_name !== undefined) { // 检查是否提供了user_name
        updates.push('user_name = ?');
        values.push(user_name);
    }
    if (phone_number !== undefined) { // 检查是否提供了phone_number
        updates.push('phone_number = ?');
        values.push(phone_number);
    }
    if (sex !== undefined) { // 检查是否提供了sex
        updates.push('sex = ?');
        values.push(sex);
    }
    // *** 添加对 user_img 字段的检查和更新逻辑 ***
    if (user_img !== undefined) { // 检查是否提供了user_img
        updates.push('user_img = ?');
        values.push(user_img);
    }

    // 如果没有要更新的字段，直接返回
    if (updates.length === 0) {
        // 如果只有 user_id，说明没有其他字段需要更新，也返回成功
         return res.json({ success: true, message: '没有需要更新的字段' });
    }

    sql += ' ' + updates.join(', ') + ' WHERE user_id = ?';
    values.push(user_id);

    console.log('Update User SQL:', sql);
    console.log('Update User Values:', values);

    connection.query(sql, values, (err, result) => {
        if (err) {
            console.error('更新用户详情失败:', err);
            return res.status(500).json({ success: false, message: '更新用户详情失败' });
        }

        console.log('更新用户详情成功:', result);

        // 检查是否有一行被影响，表示更新成功
        if (result.affectedRows > 0) {
            res.json({ success: true, message: '用户详情更新成功' });
        } else {
            // 如果affectedRows为0，可能user_id不存在匹配项或数据无变化
            console.warn('未找到匹配的用户记录进行详情更新或数据无变化:', { user_id });
            res.json({ success: false, message: '未找到匹配的用户记录或数据无变化' });
        }
    });
});

// 编写文件上传接口 (uploadAvatar)
// 使用 upload.single('avatar') 中间件来处理单个文件上传，'avatar' 是小程序端 wx.uploadFile 的 name 字段值
app.post('/uploadAvatar', upload.single('avatar'), (req, res) => {
    // 文件上传成功后，文件信息存储在 req.file 中
    if (!req.file) {
        return res.status(400).json({ success: false, message: '未接收到文件' });
    }

    // 构建文件访问 URL
    // 假设你的后端服务器可以通过 /uploads 路径访问到 uploads 文件夹中的文件
    // 你可能需要在 Express 中配置静态文件服务来提供 uploads 文件夹的访问
    const fileUrl = `http://127.0.0.1:${port}/uploads/${req.file.filename}`;

    console.log('文件上传成功，访问地址:', fileUrl);

    // 返回文件访问地址给小程序端
    res.json({ success: true, avatarUrl: fileUrl, message: '头像上传成功' });
});

// 添加用户习惯
app.post('/addUserHabit', (req, res) => {
  console.log('req.body:', req.body);
  const {
    userId,
    habit_name,
    habit_img,
    habit_createdTime,
    habit_finishedTime,
    habit_customTime,
    habit_num,
    isClockedInToday,
    is_finished,
    punch_in_scenarios,
    habit_frequency_type,
    habit_frequency_value,
    habit_frequency_days
  } = req.body;

  // 参数验证
  if (!userId || !habit_name) {
    return res.status(400).json({
      success: false,
      message: '缺少必要参数'
    });
  }

  // 构建SQL插入语句
  const sql = `
    INSERT INTO user_habit (
      user_id,
      habit_name,
      habit_img,
      habit_createdTime,
      habit_finishedTime,
      habit_customTime,
      habit_num,
      isClockedInToday,
      is_finished,
      punch_in_scenarios,
      habit_frequency_type,
      habit_frequency_value,
      habit_frequency_days
    ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
  `;

  const values = [
    userId,
    habit_name,
    habit_img || null,
    habit_createdTime || null,
    habit_finishedTime || null,
    habit_customTime || 0,
    habit_num || 0,
    isClockedInToday || 0,
    is_finished || 0,
    punch_in_scenarios || null,
    habit_frequency_type !== undefined ? habit_frequency_type : 1,
    habit_frequency_value !== undefined ? habit_frequency_value : 0,
    habit_frequency_days || ''
  ];

  console.log('Executing addUserHabit with values:', values);

  connection.query(sql, values, (err, result) => {
    if (err) {
      console.error('添加用户习惯失败:', err);
      return res.status(500).json({
        success: false,
        message: '添加用户习惯失败'
      });
    }
    res.json({
      success: true,
      message: '添加用户习惯成功',
      insertId: result.insertId
    });
  });
});

// 获取单个用户习惯详情
app.get('/getUserHabitDetail', (req, res) => {
  const userId = req.query.userId || req.query.user_id;
  const habitId = req.query.habitId || req.query.habit_id;
  if (!userId || !habitId) {
    return res.status(400).json({
      success: false,
      message: '缺少必要参数 userId 或 habitId'
    });
  }
  const sql = 'SELECT * FROM user_habit WHERE user_id = ? AND habit_id = ?';
  connection.query(sql, [userId, habitId], (err, results) => {
    if (err) {
      console.error('查询用户习惯详情失败:', err);
      return res.status(500).json({
        success: false,
        message: '数据库查询失败'
      });
    }
    if (results.length === 0) {
      return res.status(404).json({
        success: false,
        message: '未找到该用户的该习惯'
      });
    }
    res.json(results[0]);
  });
});

// 获取指定习惯在特定月份的打卡次数
app.get('/getHabitMonthCount', (req, res) => {
  const { userId, habitId, year, month } = req.query;

  if (!userId || !habitId || !year || !month) {
    return res.status(400).json({ success: false, message: '缺少必要参数 (userId, habitId, year, month)' });
  }

  const sql = 'SELECT COUNT(*) AS count FROM log WHERE user_id = ? AND habit_id = ? AND log_year = ? AND log_month = ?';
  const values = [userId, habitId, year, month];

  connection.query(sql, values, (err, result) => {
    if (err) {
      console.error('获取习惯月度打卡次数失败:', err);
      return res.status(500).json({ success: false, message: '获取习惯月度打卡次数失败', error: err.message });
    }

    // result[0].count 将是查询到的次数
    const count = result.length > 0 ? result[0].count : 0;
    res.json({ success: true, count: count });
  });
});

// 获取某用户某习惯所有打卡日志（含 log_type、年月日）
app.get('/getHabitRecords', (req, res) => {
  const userId = req.query.userId || req.query.user_id;
  const habitId = req.query.habitId || req.query.habit_id;
  if (!userId || !habitId) {
    return res.status(400).json({
      success: false,
      message: '缺少必要参数 userId 或 habitId'
    });
  }
  const sql = 'SELECT log_year, log_month, log_day, log_type FROM log WHERE user_id = ? AND habit_id = ?';
  connection.query(sql, [userId, habitId], (err, results) => {
    if (err) {
      console.error('查询打卡日志失败:', err);
      return res.status(500).json({
        success: false,
        message: '数据库查询失败'
      });
    }
    res.json({ records: results });
  });
});

app.post('/deleteLog', (req, res) => {
  const { habit_id, user_id, log_year, log_month, log_day } = req.body;
  if (!habit_id || !user_id || !log_year || !log_month || !log_day) {
    return res.json({ success: false, message: '缺少参数' });
  }
  const sql = 'DELETE FROM log WHERE habit_id=? AND user_id=? AND log_year=? AND log_month=? AND log_day=?';
  const values = [habit_id, user_id, log_year, log_month, log_day];
  connection.query(sql, values, (err) => {
    if (err) {
      res.json({ success: false, message: '删除失败' });
    } else {
      res.json({ success: true });
    }
  });
});

// 获取某用户某习惯的所有日志
app.get('/getHabitLogs', (req, res) => {
  const user_id = req.query.user_id;
  const habit_id = req.query.habit_id;
  if (!user_id || !habit_id) {
    return res.status(400).json({ success: false, message: '缺少必要参数' });
  }
  const sql = 'SELECT log_id, log_content, log_img, log_year, log_month, log_day, log_CreatedTime, log_type FROM log WHERE user_id = ? AND habit_id = ? ORDER BY log_year DESC, log_month DESC, log_day DESC, log_id DESC';
  connection.query(sql, [user_id, habit_id], (err, results) => {
    if (err) {
      console.error('获取日志失败:', err);
      return res.status(500).json({ success: false, message: '数据库查询失败' });
    }
    res.json({ logs: results });
  });
});

// 通过 log_id 删除日志
app.post('/deleteLogById', (req, res) => {
  const { log_id } = req.body;
  if (!log_id) {
    return res.status(400).json({ success: false, message: '缺少 log_id' });
  }
  const sql = 'DELETE FROM log WHERE log_id = ?';
  connection.query(sql, [log_id], (err) => {
    if (err) {
      console.error('删除日志失败:', err);
      return res.status(500).json({ success: false, message: '数据库删除失败' });
    }
    res.json({ success: true });
  });
});


// 更新习惯重要状态
app.post('/updateHabitImportant', (req, res) => {
  const { userId, habitId, is_important } = req.body;
  console.log(`Received request to update habit important status: userId=${userId}, habitId=${habitId}, is_important=${is_important}`);

  if (userId === undefined || habitId === undefined || is_important === undefined) {
    return res.status(400).json({ success: false, message: '参数不完整' });
  }

  // 将布尔值转换为整数 (0 或 1)
  const importantValue = is_important ? 1 : 0;

  const sql = 'UPDATE user_habit SET is_important = ? WHERE habit_id = ? AND user_id = ?';
  connection.query(sql, [importantValue, habitId, userId], (err, result) => {
    if (err) {
      console.error('更新习惯重要状态失败:', err);
      return res.status(500).json({ success: false, message: '服务器内部错误' });
    }

    if (result.affectedRows === 0) {
      return res.json({ success: false, message: '未找到要更新的习惯或状态未改变' });
    }

    res.json({ success: true, message: '习惯重要状态更新成功' });
  });
});

// 更新用户习惯
app.post('/updateUserHabit', (req, res) => {
  const { 
    habit_id, 
    user_id, 
    habit_name, 
    habit_img, 
    habit_finishedTime, 
    habit_customTime, 
    punch_in_scenarios, 
    habit_frequency_type, 
    habit_frequency_value, 
    habit_frequency_days,
    is_important
  } = req.body;

  // 参数验证
  if (!habit_id || !user_id) {
    return res.status(400).json({ success: false, message: '缺少习惯ID或用户ID' });
  }

  let sql = 'UPDATE user_habit SET';
  const values = [];
  const updates = [];

  if (habit_name !== undefined) {
    updates.push('habit_name = ?');
    values.push(habit_name);
  }
  if (habit_img !== undefined) {
    updates.push('habit_img = ?');
    values.push(habit_img);
  }
  if (habit_finishedTime !== undefined) {
    updates.push('habit_finishedTime = ?');
    values.push(habit_finishedTime);
  }
  if (habit_customTime !== undefined) {
    updates.push('habit_customTime = ?');
    values.push(habit_customTime);
  }
  if (punch_in_scenarios !== undefined) {
    updates.push('punch_in_scenarios = ?');
    values.push(punch_in_scenarios);
  }
  if (habit_frequency_type !== undefined) {
    updates.push('habit_frequency_type = ?');
    values.push(habit_frequency_type);
  }
  if (habit_frequency_value !== undefined) {
    updates.push('habit_frequency_value = ?');
    values.push(habit_frequency_value);
  }
  if (habit_frequency_days !== undefined) {
    updates.push('habit_frequency_days = ?');
    values.push(habit_frequency_days);
  }
  if (is_important !== undefined) {
    updates.push('is_important = ?');
    values.push(is_important);
  }

  // 如果没有需要更新的字段，直接返回成功
  if (updates.length === 0) {
    return res.json({ success: true, message: '没有需要更新的字段' });
  }

  sql += ' ' + updates.join(', ') + ' WHERE habit_id = ? AND user_id = ?';
  values.push(habit_id, user_id);

  console.log('Update Habit SQL:', sql);
  console.log('Update Habit Values:', values);

  connection.query(sql, values, (err, result) => {
    if (err) {
      console.error('更新习惯失败:', err);
      return res.status(500).json({ success: false, message: '更新习惯失败', error: err.message });
    }

    console.log('习惯更新成功:', result);
    if (result.affectedRows > 0) {
      res.json({ success: true, message: '习惯更新成功' });
    } else {
      res.json({ success: false, message: '未找到匹配的习惯进行更新或数据无变化' });
    }
  });
});

// 获取某用户某习惯某年某周的打卡次数（周一为一周的开始）
app.get('/getHabitWeekCount', (req, res) => {
  const userId = req.query.userId || req.query.user_id;
  const habitId = req.query.habitId || req.query.habit_id;
  const year = parseInt(req.query.year);
  const week = parseInt(req.query.week);
  if (!userId || !habitId || !year || !week) {
    return res.status(400).json({
      success: false,
      message: '缺少必要参数 userId, habitId, year, week'
    });
  }

  // 计算该年第几周的起止日期（周一为一周的第一天）
  function getDateOfISOWeek(week, year) {
    const simple = new Date(year, 0, 1 + (week - 1) * 7);
    const dow = simple.getDay();
    let ISOweekStart = simple;
    if (dow <= 4 && dow !== 0) {
      ISOweekStart.setDate(simple.getDate() - simple.getDay() + 1);
    } else {
      ISOweekStart.setDate(simple.getDate() + 8 - simple.getDay());
    }
    ISOweekStart.setHours(0,0,0,0);
    const ISOweekEnd = new Date(ISOweekStart);
    ISOweekEnd.setDate(ISOweekStart.getDate() + 6);
    ISOweekEnd.setHours(23,59,59,999);
    return { start: ISOweekStart, end: ISOweekEnd };
  }

  const { start, end } = getDateOfISOWeek(week, year);
  // 构建 SQL 查询，统计 log_type=1 的天数（去重）
  const sql = `
    SELECT COUNT(DISTINCT CONCAT(log_year, '-', log_month, '-', log_day)) AS count
    FROM log
    WHERE user_id = ? AND habit_id = ? AND log_type = 1
      AND STR_TO_DATE(CONCAT(log_year, '-', LPAD(log_month,2,'0'), '-', LPAD(log_day,2,'0')), '%Y-%m-%d')
        BETWEEN ? AND ?
  `;
  connection.query(sql, [userId, habitId, start.toISOString().slice(0,10), end.toISOString().slice(0,10)], (err, results) => {
    if (err) {
      console.error('查询本周打卡次数失败:', err);
      return res.status(500).json({
        success: false,
        message: '数据库查询失败'
      });
    }
    res.json({ count: results[0].count });
  });
});

// 查询用户积分
app.get('/getUserScore', (req, res) => {
  const userId = req.query.userId;
  if (!userId) {
    return res.status(400).json({ success: false, message: '缺少用户ID' });
  }
  const sql = 'SELECT score FROM user WHERE user_id = ?';
  connection.query(sql, [userId], (err, results) => {
    if (err) {
      return res.status(500).json({ success: false, message: '数据库查询失败' });
    }
    if (results.length === 0) {
      return res.status(404).json({ success: false, message: '未找到用户' });
    }
    res.json({ success: true, score: results[0].score });
  });
});

// 更新用户积分（delta为正数加积分，负数扣积分）
app.post('/updateUserScore', (req, res) => {
  const { userId, delta } = req.body;
  if (!userId || typeof delta !== 'number') {
    return res.status(400).json({ success: false, message: '缺少参数' });
  }
  // 开启事务
  connection.beginTransaction(err => {
    if (err) {
      console.error('开启事务失败:', err);
      return res.status(500).json({ success: false, message: '开启事务失败' });
    }
    // 先查当前积分
    connection.query('SELECT score FROM user WHERE user_id = ?', [userId], (err, results) => {
      if (err) {
        return connection.rollback(() => {
          res.status(500).json({ success: false, message: '数据库查询失败' });
        });
      }
      if (results.length === 0) {
        return connection.rollback(() => {
          res.status(404).json({ success: false, message: '未找到用户' });
        });
      }
      const currentScore = results[0].score;
      if (delta<0&&currentScore + delta < 0) {
        return connection.rollback(() => {
          res.json({ success: false, message: '积分不足' });
        });
      }
      // 更新积分
      connection.query('UPDATE user SET score = score + ? WHERE user_id = ?', [delta, userId], (err2) => {
        if (err2) {
          return connection.rollback(() => {
            res.status(500).json({ success: false, message: '更新失败' });
          });
        }
        // 自动插入积分明细
        const type = delta > 0 ? '加分' : '扣分';
        const desc = delta > 0 ? '每日打卡' : '补卡消耗';
        connection.query('INSERT INTO score_log (user_id, `change`, type, `desc`) VALUES (?, ?, ?, ?)', [userId, delta, type, desc], (err3) => {
          if (err3) {
            console.error('插入积分明细失败:', err3);
            return connection.rollback(() => {
              res.status(500).json({ success: false, message: '积分明细插入失败' });
            });
          }
          // 提交事务
          connection.commit(err => {
            if (err) {
              return connection.rollback(() => {
                res.status(500).json({ success: false, message: '提交事务失败' });
              });
            }
            res.json({ success: true });
          });
        });
      });
    });
  });
});

// 获取用户积分明细
app.get('/getUserScoreLogs', (req, res) => {
  const userId = req.query.userId || req.query.user_id;
  if (!userId) {
    return res.status(400).json({ success: false, message: '缺少用户ID' });
  }
  const sql = 'SELECT id, `change`, type, `desc`, created_at FROM score_log WHERE user_id = ? ORDER BY created_at DESC';
  connection.query(sql, [userId], (err, results) => {
    if (err) {
      console.error('查询积分明细失败:', err);
      return res.status(500).json({ success: false, message: '数据库查询失败' });
    }
    // 格式化返回
    const logs = results.map(item => ({
      id: item.id,
      score: (item.change > 0 ? '+' : '') + item.change,
      type: item.type,
      desc: item.desc,
      time: new Date(new Date(item.created_at).getTime() + 8 * 60 * 60 * 1000)
              .toISOString().replace('T', ' ').slice(0, 19)
    }));
    res.json({ success: true, logs });
  });
});

// 新增积分明细
app.post('/addScoreLog', (req, res) => {
  const { user_id, change, type, desc } = req.body;
  if (!user_id || !change || !type) {
    return res.status(400).json({ success: false, message: '缺少必要参数' });
  }
  const sql = 'INSERT INTO score_log (user_id, `change`, type, `desc`) VALUES (?, ?, ?, ?)';
  connection.query(sql, [user_id, change, type, desc || null], (err, result) => {
    if (err) {
      console.error('插入积分明细失败:', err);
      return res.status(500).json({ success: false, message: '数据库插入失败' });
    }
    res.json({ success: true, id: result.insertId });
  });
});

// 新增：更新日志内容和图片（只改内容不覆盖图片，只改图片不覆盖内容，返回更新后的数据）
app.post('/updateLog', (req, res) => {
  const { log_id, log_content, log_img } = req.body;
  if (!log_id) {
    return res.status(400).json({ success: false, message: '缺少 log_id' });
  }
  // 构建动态 SQL
  let sql = 'UPDATE log SET';
  const updates = [];
  const values = [];
  if (log_content !== undefined) {
    updates.push('log_content = ?');
    values.push(log_content);
  }
  if (log_img !== undefined) {
    updates.push('log_img = ?');
    values.push(log_img);
  }
  if (updates.length === 0) {
    return res.status(400).json({ success: false, message: '没有需要更新的字段' });
  }
  sql += ' ' + updates.join(', ') + ' WHERE log_id = ?';
  values.push(log_id);
  connection.query(sql, values, (err) => {
    if (err) {
      return res.status(500).json({ success: false, message: '数据库更新失败' });
    }
    // 查询更新后的数据
    connection.query('SELECT * FROM log WHERE log_id = ?', [log_id], (err2, rows) => {
      if (err2) {
        return res.status(500).json({ success: false, message: '查询更新后数据失败' });
      }
      res.json({ success: true, log: rows[0] });
    });
  });
});

